package com.fitness.dao;

import com.fitness.entity.Performance;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class PerformanceDAO extends BaseDAO {

    /**
     * 查询所有项目经理的年度绩效
     * 关联：项目经理表 → 项目表 → 消费记录表
     * 按 经理姓名、年份 分组统计消费金额
     */
    public List<Performance> getManagerAnnualPerformance() {
        List<Performance> performanceList = new ArrayList<>();
        String sql = "SELECT " +
                "    pm.经理姓名, " +
                "    YEAR(cr.消费时间) AS 年份, " +
                "    SUM(cr.实际金额) AS 年度消费总额 " +
                "FROM 项目经理表 pm " +
                "JOIN 项目表 p ON pm.负责项目ID = p.项目ID " + // 项目经理关联项目
                "JOIN 消费记录表 cr ON p.项目ID = cr.项目ID " + // 项目关联消费记录
                "GROUP BY pm.经理姓名, YEAR(cr.消费时间) " +
                "ORDER BY pm.经理姓名, 年份";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                Performance performance = new Performance();
                performance.setManagerName(rs.getString("经理姓名"));
                performance.setYear(rs.getInt("年份"));
                performance.setTotalAmount(rs.getDouble("年度消费总额"));
                performanceList.add(performance);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return performanceList;
    }
}